package gavdi.dk.data;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;


import gavdi.dk.model.Absence;
import gavdi.dk.model.AbsenceRegistration;
import gavdi.dk.model.Customer;
import gavdi.dk.model.Event;
import gavdi.dk.model.TimeRegistration;



import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v4.content.CursorLoader;


public class DAOEvent {

	  DAOCustomer dCustomer = new DAOCustomer(null);
	  DAOAbsence dAbsence = new DAOAbsence(null);

  // Database fields
  private SQLiteDatabase database;
  private MySQLiteHelper dbHelper;
  private String[] allColumns = { 
		  
		  MySQLiteHelper.COLUMN_ID,
      MySQLiteHelper.COLUMN_CUSORABS,
      MySQLiteHelper.COLUMN_USERID,
      MySQLiteHelper.COLUMN_DATE,
      MySQLiteHelper.COLUMN_STATUS,
      MySQLiteHelper.COLUMN_COMMENT,
      MySQLiteHelper.COLUMN_TYPE,
      MySQLiteHelper.COLUMN_HOURS
      };





  public DAOEvent(Context context) {
    dbHelper = new MySQLiteHelper(context);
  }

  public void open() throws SQLException {
    database = dbHelper.getWritableDatabase();
  }

  public void close() {
    dbHelper.close();
  }

  public int createEvent(Event event, String userID) {
	  open();
    ContentValues values = new ContentValues();
    
    
    String type = event.getType();
   // System.out.println("TYPE:"+type);
    if(type == "T")
    {

	    TimeRegistration tr = (TimeRegistration) event;
	    values.put(MySQLiteHelper.COLUMN_CUSORABS, tr.getCustomer().getWbs());
	    values.put(MySQLiteHelper.COLUMN_USERID, userID);
	    values.put(MySQLiteHelper.COLUMN_DATE, tr.getDate());
	    values.put(MySQLiteHelper.COLUMN_STATUS, tr.getStatus());
	    values.put(MySQLiteHelper.COLUMN_COMMENT, tr.getComment());
	    values.put(MySQLiteHelper.COLUMN_TYPE, tr.getType());
	    values.put(MySQLiteHelper.COLUMN_HOURS, tr.getHours());

    }
    else if(type == "A"){
    	
        AbsenceRegistration ar = (AbsenceRegistration) event;
	    values.put(MySQLiteHelper.COLUMN_CUSORABS, ar.getAbsence().getCode());
	    values.put(MySQLiteHelper.COLUMN_USERID, userID);
        values.put(MySQLiteHelper.COLUMN_DATE, ar.getDate());
        values.put(MySQLiteHelper.COLUMN_STATUS, ar.getStatus());
        values.put(MySQLiteHelper.COLUMN_COMMENT, ar.getComment());
        values.put(MySQLiteHelper.COLUMN_TYPE, ar.getType());
        values.put(MySQLiteHelper.COLUMN_HOURS, ar.getHours());
    }
 

    int res = (int)database.insert(MySQLiteHelper.TABLE_EVENTS, null,
        values);
    close();
    return res;
  }

  public void deleteEvent(Event event) {
	  open();
    String id = event.getId();
    System.out.println("Event deleted with id: " + id);
    database.delete(MySQLiteHelper.TABLE_EVENTS, MySQLiteHelper.COLUMN_ID
        + " = " + id, null);
    close();
  }

//  public Event getEvent(String eventID) {
//	  open();
//	  	Cursor cursor = database.query(MySQLiteHelper.TABLE_EVENTS,
//        allColumns, MySQLiteHelper.COLUMN_ID + "="+eventID, null, null, null, null);
//    
//
//        cursor.moveToFirst();
//        
//        Event event = cursorToEvent(cursor);
//       
//      // Make sure to close the cursor
//      cursor.close();
//      
//
//      close();
//    return event;
//
//    
//  }
  
  

  public ArrayList<Event> getAllEvents() {
	  open();
	    ArrayList<Event> events = new ArrayList<Event>();

	    Cursor cursor = database.query(MySQLiteHelper.TABLE_EVENTS,
	        allColumns, null, null, null, null, null);

	    cursor.moveToFirst();
	    while (!cursor.isAfterLast()) {
	    	Event event = cursorToEvent(cursor);
	    	events.add(event);
	      cursor.moveToNext();
	    }
	    // Make sure to close the cursor
	    cursor.close();
	    close();
	    return events;
	  }
  
  public Cursor getAllCursorEvents() {
	  open();


	    Cursor cursor = database.query(MySQLiteHelper.TABLE_EVENTS,
	        allColumns, null, null, null, null, null);

	    

	  
	    return cursor;
	  }

  
  public List<Event> getAmountOfEventsByUserID(String userID, String amount) {
	  open();
	  List<Event> events = new ArrayList<Event>();	  

	  
	  String MY_QUERY3 = 
			"SELECT * from " +
	  		"(SELECT events._id a, events.cusorabs b, events.userID c, " +
	  		"events.date d, events.status e, events.comment f, events.type g, " +
	  		"events.hours h, customer.name i " +
			"FROM events, customer " +
			"WHERE events.userID = " + "\""+userID+"\"" +
			" AND events.cusorabs = customer.wbs " +
			"UNION "+
			"SELECT events._id a, events.cusorabs b, events.userID c, " +
			"events.date d, events.status e, events.comment f, events.type g, " +
			"events.hours h, absence.name i " +
			"FROM events, absence " +
			"WHERE events.userID = \""+userID+"\" " + 
			"AND events.cusorabs = absence.code) test " +
			"order by test.a DESC" +
			" LIMIT " +  amount;
  


	  Cursor cursor = database.rawQuery(MY_QUERY3, null);

	  cursor.moveToFirst();

	  while (!cursor.isAfterLast()) {
		  
	  Event event = cursorToEvent(cursor);
	  
	  events.add(event);
	  

	  cursor.moveToNext();

	  }

	  // Make sure to close the cursor
	  cursor.close();
	  close();
	  return events;

	  }
  
  
  public List<Event> getAllEventsByUserID(String userID) {
	  open();
	  List<Event> events = new ArrayList<Event>();	  

	  
	  String MY_QUERY3 = 
			"SELECT * from " +
	  		"(SELECT events._id a, events.cusorabs b, events.userID c, " +
	  		"events.date d, events.status e, events.comment f, events.type g, " +
	  		"events.hours h, customer.name i " +
			"FROM events, customer " +
			"WHERE events.userID = " + "\""+userID+"\"" +
			" AND events.cusorabs = customer.wbs " +
			"UNION "+
			"SELECT events._id a, events.cusorabs b, events.userID c, " +
			"events.date d, events.status e, events.comment f, events.type g, " +
			"events.hours h, absence.name i " +
			"FROM events, absence " +
			"WHERE events.userID = \""+userID+"\" " + 
			"AND events.cusorabs = absence.code) test " +
			"order by test.a DESC";


	  Cursor cursor = database.rawQuery(MY_QUERY3, null);

	  cursor.moveToFirst();

	  while (!cursor.isAfterLast()) {
		  
	  Event event = cursorToEvent(cursor);
	  
	  events.add(event);
	  

	  cursor.moveToNext();

	  }

	  // Make sure to close the cursor
	  cursor.close();
	  close();
	  return events;

	  }
  
  public boolean exists(String id){
	  boolean res = false;
	  open();
	  Cursor cursor = 	database.query(MySQLiteHelper.TABLE_EVENTS,
		        		new String[]{MySQLiteHelper.COLUMN_ID}, 
		        		MySQLiteHelper.COLUMN_ID+"="+id, null, null, null, null);
	  res = cursor.moveToFirst();
	  cursor.close();
	  close();
	  return res;
  }



  private Event cursorToEvent(Cursor cursor) {
	  String type = cursor.getString(6);

	 System.out.println(cursor.getString(7));
	// System.out.println(cursor.getString(8));
	  
	  if(type.equalsIgnoreCase("T")){
		  Customer customer = new Customer();
		  customer.setWbs(cursor.getString(1));
		  customer.setName(cursor.getString(8));
		  TimeRegistration tr = new TimeRegistration();
		  tr.setCustomer(customer);
		  tr.setId(Integer.toString(cursor.getInt(0)));
		  tr.setUserID(cursor.getString(2));
		  tr.setDate(cursor.getString(3));
		  tr.setStatus(cursor.getString(4));
		  tr.setType(type);
		  tr.setComment(cursor.getString(5));
		  tr.setHours(cursor.getString(7));
		  

		  return tr;
	  }
	  else if(type.equalsIgnoreCase("A")) {
		  Absence abs = new Absence();
		  AbsenceRegistration ar = new AbsenceRegistration();
		  abs.setCode(cursor.getString(1));
		  abs.setName(cursor.getString(8));
		  ar.setAbsence(abs);
		  ar.setId(Integer.toString(cursor.getInt(0)));
		  ar.setUserID(cursor.getString(2));
		  ar.setDate(cursor.getString(3));
		  ar.setStatus(cursor.getString(4));
		  ar.setComment(cursor.getString(5));
		  ar.setType(type);
		  ar.setHours(cursor.getString(7));
		  return ar;
	  }

    return null;
  }
  


  private Event cursorToEventWithCus(Cursor cursor ) {
	  String type = cursor.getString(6);

	  
	  if(type.equalsIgnoreCase("T")){
		  Customer customer = new Customer();

		  customer.setWbs(cursor.getString(10));
		  
		  TimeRegistration tr = new TimeRegistration();
		  tr.setCustomer(customer);

		  tr.setId(cursor.getString(0));
		  tr.setUserID(cursor.getString(2));
		  tr.setDate(cursor.getString(3));
		  tr.setStatus(cursor.getString(4));
		  tr.setType(type);
		  tr.setComment(cursor.getString(5));
		  tr.setHours(cursor.getString(7));
		  
		  return tr;
	  }
	  else if(type.equalsIgnoreCase("A")) {
		  Absence abs = new Absence();
		  abs.setName(cursor.getString(9));
		  abs.setCode(cursor.getString(11));
		  
		  AbsenceRegistration ar = new AbsenceRegistration();
		 
		  ar.setAbsence(abs);
		  ar.setId(Integer.toString(cursor.getInt(0)));
		  ar.setUserID(cursor.getString(2));
		  ar.setDate(cursor.getString(3));
		  ar.setStatus(cursor.getString(4));
		  ar.setComment(cursor.getString(5));
		  ar.setType(type);
		  ar.setHours(cursor.getString(7));
		  
		  return ar;
	  }

    return null;
  }
} 